*********************************************************************
* This is the file used to construct the numbers in calibration.
*
*********************************************************************

clear
*cd "C:\Users\zhuy\Dropbox\US_bank_data" 
*cd "B:\Dropbox\US_bank_data"
cd "/Users/yuzhu/Dropbox/US_bank_data"
use "call_reports/callreports.dta"
*drop if intexp<0
drop if year<=1986 | year>2019
gen year_quarter = (year-1987)*4+quarter

***************************************************************
* Generate asset weighted average cost on managing assets
***************************************************************
bysort year_quarter: egen avg_expenses = mean(nonintexp-exponpremises)
bysort year_quarter: egen avg_assets= mean(assets)
bysort year_quarter: egen avg_assets1= mean(deposits+foreigndep)
* generate the aggregate transaction balances
bysort year_quarter: egen totaltransdep = total(transdep)  
*bysort year_quarter: egen avg_operinc = mean(operinc)
*bysort year_quarter: egen avg_opercost = mean(intandnonintexp)
*bysort year_quarter: egen avg_premises = mean(exponpremises)

gen profit = operinc - intandnonintexp+exponpremises
gen avg_cost = avg_expenses/avg_assets
gen avg_cost1 = avg_expenses/avg_assets1

gen premises = exponpremises/assets
gen prod = operinc/assets
gen prof = profit/assets
gen prem2inc = exponpremises/operinc
gen prem2profit = exponpremises/profit
gen prem2netintinc=exponpremises/intincnet
gen prem2netintincdeposits = exponpremises/(intincloans-intexpalldep)
gen prem2intincassets=exponpremises/intincassets
bysort year: egen annual_avg_cost = mean(avg_cost*4)
bysort year: egen annual_avg_cost1 = mean(avg_cost1*4/1.505)   /* generate average deposit handling cost used in the paper*/

bysort rssdid year: egen annual_prod = mean(prod*4)
bysort rssdid year: egen annual_prof = mean(prof*4)
bysort rssdid year: egen annual_prem = mean(premises*4)

gen logassets=log(assets)

gen ratio = avg_assets/avg_assets1

**********************************************
* Regression evidence of fixed operating costs
**********************************************
gen assets1 = assets/1e9


estimates clear

qui:reg prod assets1 if  assets>6367 & prof>-0.0086,r 
estimates store m1

qui:reg prof assets1 if  assets>6367 & prof>-0.0086, r
estimates store m4


qui:xtset rssdid year_quarter
qui:xtreg prod assets1 if  assets>6367 & prof>-0.0086,fe r
estimates store m2

qui:xtreg prof assets1 if  assets>6367 & prof>-0.0086,fe r
estimates store m5


qui:xtreg prod i.year_quarter assets1 if  assets>6367 & prof>-0.0086,fe r
estimates store m3

qui:xtreg prof i.year_quarter assets1 if  assets>6367 & prof>-0.0086,fe r
estimates store m6


esttab m1 m2 m3 m4 m5 m6 using "tables/operating_costs.tex",  keep(assets1) cells(b(star fmt(4)) se(par fmt(4))) stats(r2 r2_a p_value N)  starlevels(* 0.10 ** 0.05 *** 0.01) label legend replace

**********************************************************
* Log assets evidence
**********************************************************

estimates clear

qui:reg prod logassets if  assets>6367 & prof>-0.0086,r 
estimates store m1

qui:reg prof logassets if  assets>6367 & prof>-0.0086, r
estimates store m4


qui:xtset rssdid year_quarter
qui:xtreg prod logassets if  assets>6367 & prof>-0.0086,fe r
estimates store m2

qui:xtreg prof logassets if  assets>6367 & prof>-0.0086,fe r
estimates store m5


qui:xtreg prod i.year_quarter logassets if  assets>6367 & prof>-0.0086,fe r
estimates store m3

qui:xtreg prof i.year_quarter logassets if  assets>6367 & prof>-0.0086,fe r
estimates store m6


esttab m1 m2 m3 m4 m5 m6 using "tables/operating_costs_log.tex",  keep(assets1) cells(b(star fmt(4)) se(par fmt(4))) stats(r2 r2_a p_value N)  starlevels(* 0.10 ** 0.05 *** 0.01) label legend replace




**********************************************************
* deposit rate and bank size
*********************************************************

gen qdeprate = intexpdomdep/deposits
gen qloanrate = intincloans/qavgloans
estimates clear
qui:reg qdeprate assets1 if  assets>6367,r
qui: estimate store m1
xtset rssdid year_quarter
qui: xtreg qdeprate assets1 if assets>6367,fe r
estimate store m2
qui: xtreg qdeprate i.year_quarter assets1 if assets>6367,fe r
estimate store m3

qui:reg qloanrate assets1 if assets>6367,r
estimate store m4
qui:xtreg qloanrate assets1 if assets>6367,fe r
estimate store m5
qui:xtreg qloanrate i.year_quarter assets1 if assets>6367,fe r
estimate store m6
esttab m1 m2 m3 m4 m5 m6 using "tables/reg_rates_assets.tex",  keep(assets1) cells(b(star fmt(4)) se(par fmt(4))) stats(r2 r2_a p_value N)  starlevels(* 0.10 ** 0.05 *** 0.01) label legend replace
esttab m1 m2 m3 m4 m5 m6 using "tables/reg_rates_assets.csv",  keep(assets1) cells(b(star fmt(4)) se(par fmt(4))) stats(r2 r2_a p_value N)  starlevels(* 0.10 ** 0.05 *** 0.01) label legend replace


******************************************************************
* Generate quantity weighted average interest rate on transaction deposit
******************************************************************
bysort year_quarter: egen avg_intexptransdep = mean(intexptransdep)
bysort year_quarter: egen avg_transdep= mean(transdep)
gen avg_intrate_trans = avg_intexptransdep/avg_transdep
bysort year: egen annual_intrtrans = mean(avg_intrate_trans*4)
bysort year_quarter: egen total_trans_dep = total(transdep)
bysort year: egen total_annual_trans_dep = mean(total_trans_dep)
********************************************************************
* Generate percentiles loan rate
********************************************************************
sort year_quarter
gen loan_rate = intincloans/qavgloans
by year_quarter: egen loan_rate_med = median(loan_rate)
by year_quarter: egen loan_rate_p25 = pctile(loan_rate), p(25)
by year_quarter: egen loan_rate_p10= pctile(loan_rate), p(10)
by year_quarter: egen loan_rate_p5= pctile(loan_rate), p(5)
by year_quarter: egen loan_rate_p1= pctile(loan_rate), p(1)
by year_quarter: egen loan_rate_p4= pctile(loan_rate), p(4)
by year_quarter: egen loan_rate_p3= pctile(loan_rate), p(3)
by year_quarter: egen loan_rate_p2= pctile(loan_rate), p(2)
by year_quarter: egen loan_rate_p15= pctile(loan_rate), p(1.01)
by year_quarter: egen loan_rate_p11= pctile(loan_rate), p(1.1)

bysort year: egen annual_loan_rate_p3 = mean(loan_rate_p3*4)
bysort year: egen annual_loan_rate_p4 = mean(loan_rate_p4*4)
bysort year: egen annual_loan_rate_p5 = mean(loan_rate_p5*4)
bysort year: egen annual_loan_rate_p10 = mean(loan_rate_p10*4)
bysort year: egen annual_loan_rate_p1 = mean(loan_rate_p1*4)
bysort year: egen annual_loan_rate_p2 = mean(loan_rate_p2*4)
bysort year: egen annual_loan_rate_p15 = mean(loan_rate_p15*4)
bysort year: egen annual_loan_rate_p11 = mean(loan_rate_p11*4)
bysort year: egen annual_loan_rate_p25 = mean(loan_rate_p25*4)

*****************************************************************
* Generate quantity weighted average loan rate
*****************************************************************
bysort year_quarter: egen avg_loaninc = mean(intincloans)
bysort year_quarter: egen avg_loans= mean(qavgloans)
gen avg_loan_rate = avg_loaninc/avg_loans
bysort year: egen annual_avg_intrloan = mean(avg_loan_rate*4)

sort year
by year: drop if _n>1
*export delimited year annual_med_intrate annual_mean_intrate annual_med_intrate1 annual_mean_intrate1 using  "deposit_rates.csv", replace

twoway line annual_loan_rate_p3 annual_loan_rate_p5 annual_loan_rate_p10 annual_intrtrans annual_avg_intrloan annual_avg_cost annual_avg_cost1 year
graph save "rates.png", replace

export delimited year annual_intrtrans annual_loan_rate_p1 using  "deposit_rates.csv", replace
